﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;

namespace Usermanagment.DBConnection
{
   public class DBDatafetch
    {
        private DBConnect dbc;
		//private Boolean read = false;
		private MySqlDataReader dare;
		private MySqlCommand cmd;

        public DBDatafetch(DBConnect dbc)
        {
            this.dbc = dbc;
			cmd = new MySqlCommand();
			cmd.Connection = dbc.getCon();
        }

        public DataTable getAllData()
        {
            cmd.CommandText = "SELECT * FROM `PlayersDB`";           
			MySqlDataAdapter da = new MySqlDataAdapter(cmd);
			DataTable data = new DataTable();
			da.Fill(data);
			return data;
           
        }

	   public DataTable getAllData(String table)
		{
			cmd.CommandText = "SELECT * FROM `" + table + "`";
			MySqlDataAdapter da = new MySqlDataAdapter(cmd);
			DataTable data = new DataTable();
			da.Fill(data);
			int it = data.Columns.IndexOf("Password");
			if (it > 0)
			{
				for (int i = 0; i < data.Rows.Count; i++)
				{
					data.Rows[i][it] = "*********";
				}
			}
			return data;
		}

	   public DataTable getAllUsers()
	   {
		   cmd.CommandText = "SELECT `Username` FROM `PlayersDB`";
		   MySqlDataAdapter da = new MySqlDataAdapter(cmd);
		   DataTable data = new DataTable();
		   da.Fill(data);
		   return data;

	   }

	   public DataTable getTables()
		{
			cmd.CommandText = "SHOW Tables";
			MySqlDataAdapter da = new MySqlDataAdapter(cmd);
			DataTable dt = new DataTable();
			da.Fill(dt);
			return dt;
		}

        public String fetchPass(String username)
        {
			cmd.CommandText = "SELECT `Password` FROM `PlayersDB` WHERE `Username` ='" + username + "'";
			dare = cmd.ExecuteReader();
			String t = "";
			while (dare.Read())
			{
				t = dare.GetString(0);
			}
			dare.Close();
			return t;

        }

        public int canPlay(String username)
        {
			cmd.CommandText = "SELECT `CanPlay` FROM `PlayersDB` WHERE `Username` ='" + username + "'";

			return Convert.ToInt32(cmd.ExecuteScalar());

        }

        public int canConnect(String username)
        {
			cmd.CommandText = "SELECT `CanConnect` FROM `PlayersDB` WHERE `Username` ='" + username + "'";

			return Convert.ToInt32(cmd.ExecuteScalar());
        }

        public int getID(String username)
        {
			cmd.CommandText = "SELECT `ID` FROM `PlayersDB` WHERE `Username` ='" + username + "'";        
			return Convert.ToInt32(cmd.ExecuteScalar());
        }

        public int getChips(String username)
        {
			cmd.CommandText = "SELECT `Chips` FROM `PlayersDB` WHERE `Username` ='" + username + "'";
			return Convert.ToInt32(cmd.ExecuteScalar());

        }

		public int getWins(String username)
		{
			cmd.CommandText = "SElECT `Wins` FROM `Playerstat` WHERE `Username` ='" + username + "'";
			return Convert.ToInt32(cmd.ExecuteScalar());
		}

		public int getLoss(String username)
		{
			cmd.CommandText = "SElECT `Loss` FROM `Playerstat` WHERE `Username` ='" + username + "'";
			return Convert.ToInt32(cmd.ExecuteScalar());
		}
		
		public int gettSize()
		{
			String cmnd = "SELECT COUNT(*) FROM `PlayersDB`";
			MySqlCommand cmd = new MySqlCommand(cmnd, dbc.getCon());
			return int.Parse(cmd.ExecuteScalar().ToString());
		}
    }
}
